MySQL CREATE & DROP TABLE Statement

CREATE TABLE Statement

If we need to create a new table in the database we will be using the CREATE TABLE statement.

Syntax for creating a table

CREATE TABLE table_name (
    column_1 datatype,
    column_2 datatype,
    column_3 datatype,
   ....
);

We can create the desired columns with comma seperation. Each columns will have the column name and its column type.

Example

create table employee(empno int primary key AUTO_INCREMENT, name varchar(50), age numeric, role varchar(50), location varchar(50), salary decimal);

In the above example the empno is the primary key. We have set AUTO_INCREMENT key to automatically increment to the next value for that column.

Here the name, role and location columns is of type varchar and its size is set to 50. So it can hold 50 characters.
The salary column is declared with the type decimal.

DROP TABLE Statement

In order to drop a table (removes the entire table) from the database, we will use DROP TABLE statement.

Syntax to DROP TABLE

DROP  TABLE  table_name;

Example

DROP TABLE employees;

TRUNCATE TABLE Statement

The truncate statement is used to drop all the records in the table. The table will not be deleted in this query.

Syntax for TRUNCATE TABLE Statement

TRUNCATE  TABLE  _table_name_;

Example

TRUNCATE TABLE employees;